﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace EmployeeCheckIn
{
    public class InSertData

    {
        public static object GetDataValue(object value)
        {
            if (value == null || value=="")
            {
                return DBNull.Value;
            }

            return value;
        }
        public void Insertss(EmpEntity emp)
        {
            
            string sqltext = @"insert into EMPLOYEE 
                           ([EMP_ID]
                          ,[EMP_CODE]
                          ,[EMP_TITLE]
                          ,[EMP_FIRSTNAME]
                          ,[EMP_LASTNAME]
                          ,[EMP_POSITION_ID]
                          ,[EMP_START_DATE]
                          ,[EMP_BIRTH_DATE]
                          ,[EMP_ID_CARD]
                          ,[EMP_ADDRESS]
                          ,[EMP_PROVINCE_ID]
                          ,[EMP_POST_CODE]
                          ,[EMP_PHONE]
                          ,[EMP_STATUS_ID]
                          ,[EMP_PIC])
                           values (@emp_id,@emp_code,@emp_title,@emp_firstname,@emp_lastname,@emp_position_id
                                   ,@emp_start_date,@emp_birth_date,@emp_id_card,@emp_address,@emp_province_id,@emp_post_code,@emp_phone,@emp_status_id,@emp_pic)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            sqlcomm.Parameters.AddWithValue("@emp_id", GetDataValue(emp.ID));
            sqlcomm.Parameters.AddWithValue("@emp_code", GetDataValue(emp.EmpId));
            sqlcomm.Parameters.AddWithValue("@emp_title", GetDataValue(emp.Prefix));
            sqlcomm.Parameters.AddWithValue("@emp_firstname",GetDataValue(emp.FirstName));
            sqlcomm.Parameters.AddWithValue("@emp_lastname", GetDataValue(emp.LastName));
            sqlcomm.Parameters.AddWithValue("@emp_position_id", GetDataValue(emp.Position));
            sqlcomm.Parameters.AddWithValue("@emp_start_date", GetDataValue(emp.DateStart));
            sqlcomm.Parameters.AddWithValue("@emp_birth_date", GetDataValue(emp.DateBirth));
            sqlcomm.Parameters.AddWithValue("@emp_id_card", GetDataValue(emp.CardID));
            sqlcomm.Parameters.AddWithValue("@emp_address", GetDataValue(emp.Address));
            sqlcomm.Parameters.AddWithValue("@emp_province_id",GetDataValue(emp.City));
            sqlcomm.Parameters.AddWithValue("@emp_post_code",GetDataValue(emp.PostID));
            sqlcomm.Parameters.AddWithValue("@emp_phone", GetDataValue(emp.Phone));
            sqlcomm.Parameters.AddWithValue("@emp_status_id",GetDataValue(emp.status));
            sqlcomm.Parameters.AddWithValue("@emp_pic",GetDataValue(emp.EmpPic));
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            
        }

        public void updateTimeOfDate(string indate,string intime,string inlate, int s) 
        {
            string sqltxt = @"update  TBL_TIMEOFDAY set [TIME_OF_START]=@intime
                                                        ,[TIME_OF_LATE]=@inlate
                                                        ,[LATE_MONEY]=@latemn
							                            ,[DAY_OF_EDIT]=@indate";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltxt;
            sqlcomm.Parameters.AddWithValue("@intime",intime);
            sqlcomm.Parameters.AddWithValue("@inlate", inlate);
            sqlcomm.Parameters.AddWithValue("@latemn",s);
            sqlcomm.Parameters.AddWithValue("@indate", indate);
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        
        }

        public void InsertTime(int ID,DateTime CHECKIN,DateTime CHECKOUT,int LateTime)
        {
            string sqltext =    @"insert into TBL_CHECK_IN ([EMP_ID]
						        ,[CHECK_IN]
						        ,[CHECK_OUT]
						        ,[LATE_TIME])
	                            values ( @EMPID
                                        ,@EMPIN
                                        ,@EMPOUT
                                        ,@LATETIME)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            sqlcomm.Parameters.AddWithValue("@EMPID", ID);
            sqlcomm.Parameters.AddWithValue("@EMPIN", CHECKIN);
            sqlcomm.Parameters.AddWithValue("@EMPOUT", CHECKOUT);
            sqlcomm.Parameters.AddWithValue("@LATETIME", LateTime);
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();


            
        }

        public void UpdateTime(int ID, DateTime AfterTime, DateTime CheckOutEd, int LateTimes,DateTime BeforTime)
        {
            string sin = AfterTime.ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
            string sot = CheckOutEd.ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
            string sssu = BeforTime.ToString("yyyy-MM-dd HH:mm:ss",new System.Globalization.CultureInfo("en-US"));
            string sqltext = @"UPDATE TBL_CHECK_IN SET  CHECK_IN=@CHECK_IN
                                                        ,CHECK_OUT= @Check_out
                                                        ,LATE_TIME=@EDLATE_TIME
                                    where EMP_ID in ('" + ID + "') and CHECK_IN in ('" + sssu + "')";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            sqlcomm.Parameters.AddWithValue("@CHECK_IN", sin);
            sqlcomm.Parameters.AddWithValue("@Check_out", sot);
            sqlcomm.Parameters.AddWithValue("@EDLATE_TIME", LateTimes);

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
        }

        public void DelCheckIn(int ID,DateTime Check_IN) 
        {
            string delday = Check_IN.ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US"));
            string sqltext = @"delete TBL_CHECK_IN 
                               where check_in ='"+delday+"' and EMP_ID = ("+ID+")";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
        }
    }
}